﻿Imports System.Windows.Forms
Imports System.Collections.Specialized
Imports System.Text.RegularExpressions
Public Class ListInOut5

#Region ""
    Private WithEvents _InOutForm As New InOutForm

    Private Sub LoadData()
        Dim 开始时间 As Date = New Date(Me.dtFindDate.Value.Year, Me.dtFindDate.Value.Month, 1)
        Dim 结束时间 As Date = New Date(Me.dtFindDate1.Value.Year, Me.dtFindDate1.Value.Month, 1)
        If 开始时间 > 结束时间 Then
            Exit Sub
        End If
        Dim dt As New DataTable
        Dim dc As DataColumn
        dt.Columns.Add("用户月")
        dt.Columns.Add("月份")
        dc = dt.Columns.Add("收入", GetType(Decimal))
        dc.DefaultValue = 0
        dt.Columns.Add("支出", GetType(Decimal))
        dt.Columns.Add("统计", GetType(Decimal))
        dc = dt.Columns.Add("用户ID月")
        dc.DefaultValue = ""
        '抽数据
        Dim Year As Integer = Me.dtFindDate.Value.Year
        Dim strSql As String = String.Empty
        Dim strSql1 As String = String.Empty
        Dim dr As DataRow

        For Year = 开始时间.Year To 结束时间.Year
            Dim 开始月 As Integer = 1
            Dim 结束月 As Integer = 12
            If Year = 开始时间.Year Then
                开始月 = 开始时间.Month
            End If
            If Year = 结束时间.Year Then
                结束月 = 结束时间.Month
            End If
            For endmouth As Integer = 开始月 To 结束月
                Dim day As Integer = DateTime.DaysInMonth(Year, endmouth)
                Dim strBegin As String = Year.ToString("0000") + "-" + endmouth.ToString("00") + "-" + 1.ToString("00")
                Dim strEnd As String = Year.ToString("0000") + "-" + endmouth.ToString("00") + "-" + day.ToString("00")
                strSql = "select  sum(count) as count from WysInput where date([DateTime])>='" + strBegin + "' AND date([DateTime])<='" + strEnd + "'"
                strSql1 = "select  sum(count) as count from WysOutput where date([DateTime])>='" + strBegin + "' AND date([DateTime])<='" + strEnd + "'"

                dr = dt.NewRow

                If endmouth = 1 Then
                    dr(1) = "01月"
                ElseIf endmouth = 2 Then
                    dr(1) = "02月"
                ElseIf endmouth = 3 Then
                    dr(1) = "03月"
                ElseIf endmouth = 4 Then
                    dr(1) = "04月"
                ElseIf endmouth = 5 Then
                    dr(1) = "05月"
                ElseIf endmouth = 6 Then
                    dr(1) = "06月"
                ElseIf endmouth = 7 Then
                    dr(1) = "07月"
                ElseIf endmouth = 8 Then
                    dr(1) = "08月"
                ElseIf endmouth = 9 Then
                    dr(1) = "09月"
                ElseIf endmouth = 10 Then
                    dr(1) = "10月"
                ElseIf endmouth = 11 Then
                    dr(1) = "11月"
                ElseIf endmouth = 12 Then
                    dr(1) = "12月"
                End If
                dr(1) = Year.ToString & "年" & CStr(dr(1))
                Dim dt2 As DataTable = ct.FillTable(strSql)
                If dt2 IsNot Nothing AndAlso dt2.Rows.Count > 0 Then
                    dr(2) = 结果(dt2.Rows(0)(0))
                Else
                    dr(2) = 0
                End If
                dt2 = ct.FillTable(strSql1)
                If dt2 IsNot Nothing AndAlso dt2.Rows.Count > 0 Then
                    dr(3) = 结果(dt2.Rows(0)(0))
                Else
                    dr(3) = 0
                End If
                dr(4) = CDec(dr(2)) - CDec(dr(3))
                dt.Rows.Add(dr)
            Next
        Next

        Dim dr合计 As DataRow = dt.NewRow
        dr合计(1) = ""
        dr合计(2) = 0
        dr合计(3) = 0
        dr合计(4) = 0

        dr合计(1) = "总合计"
        For i As Integer = 0 To dt.Rows.Count - 1
            dr合计(2) += 结果(dt.Rows(i)(2))
            dr合计(3) += 结果(dt.Rows(i)(3))
            dr合计(4) += 结果(dt.Rows(i)(4))
        Next

        '合计行
        dt.Rows.Add(dr合计)

        Me.DataGridView1.DataSource = dt

        Me.DataGridView1.Columns("用户月").Visible = False
        Me.DataGridView1.Width = 537

        Me.DataGridView1.Columns("用户ID月").Visible = False
        着色(DataGridView1)
    End Sub
#End Region

    Private Sub LoadData1()
        Dim 开始时间 As Date = New Date(Me.dt开始日.Value.Year, Me.dt开始日.Value.Month, dt开始日.Value.Day)
        Dim 结束时间 As Date = New Date(Me.dt结束日.Value.Year, Me.dt结束日.Value.Month, dt结束日.Value.Day)
        If 开始时间 > 结束时间 Then
            Exit Sub
        End If

        Dim dt As New DataTable
        Dim dc As DataColumn
        dt.Columns.Add("用户日")
        dt.Columns.Add("月份")
        dc = dt.Columns.Add("收入", GetType(Decimal))
        dc.DefaultValue = 0
        dt.Columns.Add("支出", GetType(Decimal))
        dt.Columns.Add("统计", GetType(Decimal))
        dc = dt.Columns.Add("用户ID日")
        dc.DefaultValue = ""
        '抽数据
        Dim Year As Integer = Me.dtFindDate.Value.Year
        Dim endmouth As Integer = 0
        Dim strSql As String = String.Empty
        Dim strSql1 As String = String.Empty
        Dim dr As DataRow

        For Year = dt开始日.Value.Year To dt结束日.Value.Year
            Dim 开始月 As Integer = 1
            Dim 结束月 As Integer = 12
            If Year = dt开始日.Value.Year Then
                开始月 = dt开始日.Value.Month
            End If
            If Year = dt结束日.Value.Year Then
                结束月 = dt结束日.Value.Month
            End If
            For endmouth = 开始月 To 结束月
                Dim 开始日 As Integer = 1
                Dim 结束日 As Integer = DateTime.DaysInMonth(Year, endmouth)
                If Year = dt开始日.Value.Year AndAlso endmouth = dt开始日.Value.Month Then
                    开始日 = dt开始日.Value.Day
                End If
                If Year = dt结束日.Value.Year AndAlso endmouth = dt结束日.Value.Month Then
                    结束日 = dt结束日.Value.Day
                End If
                For day As Integer = 开始日 To 结束日
                    Dim strBegin As String = Year.ToString("0000") + "-" + endmouth.ToString("00") + "-" + day.ToString("00")
                    strSql = "select  sum(count) as count from WysInput where date([DateTime])='" + strBegin + "'"
                    strSql1 = "select  sum(count) as count from WysOutput where date([DateTime])='" + strBegin + "'"

                    dr = dt.NewRow
                    dr(1) = Year.ToString("0000") & "年" & endmouth.ToString("00") & "月" & day.ToString("00") & "日"
                    Dim dt2 As DataTable = ct.FillTable(strSql)
                    If dt2 IsNot Nothing AndAlso dt2.Rows.Count > 0 Then
                        dr(2) = 结果(dt2.Rows(0)(0))
                    Else
                        dr(2) = 0
                    End If
                    dt2 = ct.FillTable(strSql1)
                    If dt2 IsNot Nothing AndAlso dt2.Rows.Count > 0 Then
                        dr(3) = 结果(dt2.Rows(0)(0))
                    Else
                        dr(3) = 0
                    End If
                    dr(4) = CDec(dr(2)) - CDec(dr(3))

                    dt.Rows.Add(dr)
                Next
            Next
        Next

        Dim dr合计 As DataRow = dt.NewRow
        dr合计(1) = ""
        dr合计(2) = 0
        dr合计(3) = 0
        dr合计(4) = 0

        dr合计(1) = "总合计"
        For i As Integer = 0 To dt.Rows.Count - 1
            dr合计(2) += 结果(dt.Rows(i)(2))
            dr合计(3) += 结果(dt.Rows(i)(3))
            dr合计(4) += 结果(dt.Rows(i)(4))
        Next

        '合计行
        dt.Rows.Add(dr合计)
        Me.DataGridView2.DataSource = dt
       
        Me.DataGridView2.Columns("用户日").Visible = False
        Me.DataGridView2.Width = 537

        Me.DataGridView2.Columns("用户ID日").Visible = False
        着色(DataGridView2)
    End Sub

    Private Sub LoadData2()

        Dim date1 As DateTime = New Date(dt开始年.Value.Year, 1, 1)
        Dim date2 As DateTime = New Date(dt结束年.Value.Year, 1, 1)

        Dim dt3 As DataTable = ct.FillTable("select  sum(count) as count from WysInput")
     
        dt3 = ct.FillTable("select  sum(count) as count from WysOutput ")

        If date1 > date2 Then
            Me.DataGridView3.DataSource = Nothing
            Exit Sub
        End If

        Dim dt As New DataTable
        Dim dc As DataColumn
        dt.Columns.Add("用户")
        dt.Columns.Add("月份")
        dc = dt.Columns.Add("收入", GetType(Decimal))
        dc.DefaultValue = 0

        dt.Columns.Add("支出", GetType(Decimal))
        dt.Columns.Add("统计", GetType(Decimal))
        dc = dt.Columns.Add("用户ID")
        dc.DefaultValue = ""
        '抽数据
        Dim Year1 As Integer = Me.dt开始年.Value.Year
        Dim Year2 As Integer = Me.dt结束年.Value.Year

        Dim strSql As String = String.Empty
        Dim strSql1 As String = String.Empty
        Dim dr As DataRow

        Dim dt用户 As DataTable = ct.FillTable("select * from WysInputUser where type=0 order by UserIndex")
      
        For Year As Integer = Year1 To Year2
            Dim strBegin As String = Year.ToString("0000") + "-" + 1.ToString("00") + "-" + 1.ToString("00")
            Dim strEnd As String = Year.ToString("0000") + "-" + 12.ToString("00") + "-" + 31.ToString("00")
            strSql = "select  sum(count) as count from WysInput where date([DateTime])>='" + strBegin + "' AND date([DateTime])<='" + strEnd + "'"
            strSql1 = "select  sum(count) as count from WysOutput where date([DateTime])>='" + strBegin + "' AND date([DateTime])<='" + strEnd + "'"

            dr = dt.NewRow
            dr(0) = ""
            dr(1) = Year.ToString + "年"

            Dim dt2 As DataTable = ct.FillTable(strSql)
            If dt2 IsNot Nothing AndAlso dt2.Rows.Count > 0 Then
                dr(2) = 结果(dt2.Rows(0)(0))
            Else
                dr(2) = 0
            End If
            dt2 = ct.FillTable(strSql1)
            If dt2 IsNot Nothing AndAlso dt2.Rows.Count > 0 Then
                dr(3) = 结果(dt2.Rows(0)(0))
            Else
                dr(3) = 0
            End If
            dr(4) = CDec(dr(2)) - CDec(dr(3))
            dt.Rows.Add(dr)
        Next

        Dim dr合计 As DataRow = dt.NewRow
        dr合计(1) = ""
        dr合计(2) = 0
        dr合计(3) = 0
        dr合计(4) = 0

        dr合计(1) = "总合计"
        For i As Integer = 0 To dt.Rows.Count - 1
            dr合计(2) += 结果(dt.Rows(i)(2))
            dr合计(3) += 结果(dt.Rows(i)(3))
            dr合计(4) += 结果(dt.Rows(i)(4))
        Next

        '合计行
        dt.Rows.Add(dr合计)

        Me.DataGridView3.DataSource = dt

        Me.DataGridView3.Columns("用户").Visible = False
        Me.DataGridView3.Width = 537
        Me.DataGridView3.Columns("用户ID").Visible = False

        着色(DataGridView3)
    End Sub

    Private Function 金额(ByVal ob As Object) As Decimal
        If ob Is Nothing Then
            Return 0
        End If
        If ob Is DBNull.Value Then
            Return 0
        End If
        Return CDec(ob)
    End Function

    Private Sub ListInOut_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Dim 开始时间 As Date = New Date(Now.Year, 1, 1)
        Dim 结束时间 As Date = New Date(Now.Year, 12, 1)
        'Me.dtFindDate.Value = 开始时间
        'Me.dtFindDate1.Value = 结束时间

        开始时间 = New Date(Now.Year, Now.Month, 1)
        结束时间 = New Date(Now.Year, Now.Month, Date.DaysInMonth(Now.Year, Now.Month))
        'Me.dt开始日.Value = 开始时间
        'Me.dt结束日.Value = 结束时间

        'Me.dt开始分类.Value = 开始时间
        'Me.dt结束分类.Value = 结束时间
        'Me.dt开始分类1.Value = 开始时间
        'Me.dt结束分类1.Value = 结束时间

        '开始时间 = New Date(Now.Year, 1, 1)
        '结束时间 = New Date(Now.Year, 1, 1)
        'Me.dt开始年.Value = 开始时间
        'Me.dt结束年.Value = 结束时间

        LoadData2()
    End Sub

    Public Function 结果(ByVal ob As Object) As Decimal
        If ob Is Nothing Then
            Return 0
        End If
        If ob Is DBNull.Value Then
            ob = 0
        End If
        Return CDec(ob)
    End Function

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        LoadData()
    End Sub

    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        LoadData1()
    End Sub

    Private Sub TabControl1_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles TabControl1.SelectedIndexChanged
        If TabControl1.SelectedIndex = 0 Then
            LoadData2()
        ElseIf TabControl1.SelectedIndex = 1 Then
            LoadData()
        ElseIf TabControl1.SelectedIndex = 2 Then
            LoadData1()
        End If
    End Sub

    Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
        LoadData2()
    End Sub

    Public Sub 着色(ByVal dv As DataGridView)
        Dim dt用户 As DataTable = ct.FillTable("select * from WysInputUser where type=0 order by username")
        For Each DR As DataRow In dt用户.Rows
            Dim rn As Random = New Random()
            System.Threading.Thread.Sleep(50)
            Dim cl As System.Drawing.Color = System.Drawing.Color.FromArgb(DR("COLOR"))
            For Each dgr As DataGridViewRow In dv.Rows
                If dgr.Cells(dv.ColumnCount - 1).Value.ToString = CStr(DR("id")) Then
                    dgr.DefaultCellStyle.BackColor = cl
                End If
            Next
        Next

    End Sub

    Private Sub DataGridView3_CellDoubleClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DataGridView3.CellDoubleClick

    End Sub

    Public Function 得到支出收入窗体() As InOutForm
        If _InOutForm Is Nothing OrElse _InOutForm.IsDisposed Then
            _InOutForm = New InOutForm
        End If
        Return _InOutForm
    End Function

    Private Sub _InOutForm_FormClosing(ByVal sender As Object, ByVal e As System.Windows.Forms.FormClosingEventArgs) Handles _InOutForm.FormClosing
        收入支出ToolStripMenuItem.Checked = False
    End Sub

    Public Overrides Sub 保存窗体数据到数据库(ByRef dtData As System.Data.DataTable)
        '年
        dtData.Rows.Add(New String() {"年开始", "", dt开始年.Value.ToString("yyyy-MM-dd")})
        dtData.Rows.Add(New String() {"年结束", "", dt结束年.Value.ToString("yyyy-MM-dd")})
     
        '月
        dtData.Rows.Add(New String() {"月开始", "", dtFindDate.Value.ToString("yyyy-MM-dd")})
        dtData.Rows.Add(New String() {"月结束", "", dtFindDate1.Value.ToString("yyyy-MM-dd")})
      
        '日
        dtData.Rows.Add(New String() {"日开始", "", dt开始日.Value.ToString("yyyy-MM-dd")})
        dtData.Rows.Add(New String() {"日结束", "", dt结束日.Value.ToString("yyyy-MM-dd")})

        dtData.Rows.Add(New String() {"选择项", "", Me.TabControl1.SelectedIndex.ToString})
        MyBase.保存窗体数据到数据库(dtData)
    End Sub

    Public Overrides Sub 存取数据库数据到窗体(ByVal dtDbData As System.Data.DataTable)

        If dtDbData.Rows.Count > 0 Then
            '年
            dt开始年.Value = 查找数据("年开始", "", Now.ToString("yyyy-MM-dd"), dtDbData)
            dt结束年.Value = 查找数据("年结束", "", Now.ToString("yyyy-MM-dd"), dtDbData)
            '月
            dtFindDate.Value = 查找数据("月开始", "", Now.ToString("yyyy-MM-dd"), dtDbData)
            dtFindDate1.Value = 查找数据("月结束", "", Now.ToString("yyyy-MM-dd"), dtDbData)

            '日
            dt开始日.Value = 查找数据("日开始", "", Now.ToString("yyyy-MM-dd"), dtDbData)
            dt结束日.Value = 查找数据("日结束", "", Now.ToString("yyyy-MM-dd"), dtDbData)

            Me.TabControl1.SelectedIndex = CInt(查找数据("选择项", "", 0, dtDbData))

        End If
        MyBase.存取数据库数据到窗体(dtDbData)
    End Sub
End Class
